package com.jxt.myapp.dao.imp;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.jxt.myapp.dao.IPermissionsDao;
import com.jxt.myapp.domain.Group;
import com.jxt.myapp.domain.Menu;
import com.jxt.myapp.domain.User;
import com.jxt.myapp.domain.jqgrid.Criterion;
import com.jxt.myapp.domain.jqgrid.JqGridParameter;
import com.jxt.myapp.utils.JqGridUtils;
import com.jxt.myapp.utils.PaginationUtils;

@Repository("permissionsDao")
public class PermissionsDao implements IPermissionsDao {

	private static final Logger logger = LoggerFactory
			.getLogger(PermissionsDao.class);

	private SimpleJdbcTemplate jdbcTemplate;

	@Resource(name = "dataSource")
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	public List<Group> getAllGroup(JqGridParameter jqGridParameter) {

		String sql, sql1, sql2;
		sql1 = "SELECT id,name,remark,create_time,state,withdraw_time "
				+ "FROM group_info ";

		sql2 = " order by " + jqGridParameter.getSidx() + " "
				+ jqGridParameter.getSord() + " ";

		sql = sql1 + sql2;

//		RowMapper<Group> mapper = new RowMapper<Group>() {
//
//			public Group mapRow(ResultSet rs, int rowNum) throws SQLException {
//				Group group = new Group();
//				group.setId(rs.getString("id"));
//				group.setName(rs.getString("name"));
//				group.setRemark(rs.getString("remark"));
//				group.setCreateTime(rs.getString("create_time"));
//				group.setState(rs.getInt("state"));
//				group.setWithdrawTime(rs.getString("withdraw_time"));
//				return group;
//			}
//		};
		sql = PaginationUtils.paginationSQLForMySQL(sql,
				jqGridParameter.getPage(), jqGridParameter.getRows());
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Group>(Group.class));

	}

	public long getAllGroupCount(JqGridParameter jqGridParameter) {
		return jdbcTemplate.queryForLong("SELECT COUNT(id) FROM group_info");
	}

	public List<Group> getAllGroupByParameter(JqGridParameter jqGridParameter) {
		String sql, sql1, sql2, sql3;

		List<Criterion> criteria = new ArrayList<Criterion>();

		criteria.addAll(JqGridUtils
				.generateSearchCriteriaFromFilters(jqGridParameter.getFilters()));

		sql1 = "SELECT id,name,remark,create_time,state,withdraw_time "
				+ "FROM group_info ";

		Object[] parameters = new Object[criteria.size()];

		int index = 0;

		sql2 = JqGridUtils.converToSql(criteria, "");

		for (int i = 0; i < criteria.size(); i++) {
			parameters[index] = criteria.get(i).getValue();
			index++;
		}
		Object[] paramEnd = new Object[index];

		sql3 = " order by " + jqGridParameter.getSidx() + " "
				+ jqGridParameter.getSord() + "";

		System.arraycopy(parameters, 0, paramEnd, 0, index);

		sql = sql1 + sql2 + sql3;

		// 分页
		sql = PaginationUtils.paginationSQLForMySQL(sql,
				jqGridParameter.getPage(), jqGridParameter.getRows());

//		RowMapper<Group> mapper = new RowMapper<Group>() {
//
//			public Group mapRow(ResultSet rs, int rowNum) throws SQLException {
//				Group group = new Group();
//				group.setId(rs.getString("id"));
//				group.setName(rs.getString("name"));
//				group.setRemark(rs.getString("remark"));
//				group.setCreateTime(rs.getString("create_time"));
//				group.setState(rs.getInt("state"));
//				group.setWithdrawTime(rs.getString("withdraw_time"));
//				return group;
//			}
//		};

		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Group>(Group.class), paramEnd);
	}

	public long getAllGroupByParameterCount(JqGridParameter jqGridParameter) {
		String sql, sql1, sql2;
		sql1 = "SELECT COUNT(id) FROM group_info ";
		List<Criterion> criteria = new ArrayList<Criterion>();

		criteria.addAll(JqGridUtils
				.generateSearchCriteriaFromFilters(jqGridParameter.getFilters()));

		sql2 = JqGridUtils.converToSql(criteria, "");

		sql = sql1 + sql2;
		Object[] parameters = new Object[criteria.size()];
		int index = 0;
		for (int i = 0; i < criteria.size(); i++) {
			parameters[index] = criteria.get(i).getValue();
			index++;
		}
		Object[] paramsEnd = new Object[index];
		System.arraycopy(parameters, 0, paramsEnd, 0, index);
		return jdbcTemplate.queryForLong(sql, paramsEnd);
	}

	public List<Menu> getAllMenu() {
		String sql = "SELECT id,NAME,url,pid FROM menu";
		RowMapper<Menu> mapper = new RowMapper<Menu>() {

			public Menu mapRow(ResultSet rs, int rowNum) throws SQLException {
				Menu menu = new Menu();
				menu.setId(rs.getInt("id"));
				menu.setName(rs.getString("name"));
				menu.setPid(rs.getString("pid"));
				menu.setUrl(rs.getString("url"));
				return menu;
			}
		};
		return jdbcTemplate.query(sql, mapper);
	}

	public Group getGroupById(String id) {
		String sql = "SELECT id,name,remark,create_time,state,withdraw_time "
				+ "FROM group_info WHERE id = ?";
		RowMapper<Group> mapper = new RowMapper<Group>() {

			public Group mapRow(ResultSet rs, int rowNum) throws SQLException {
				Group group = new Group();
				group.setId(rs.getString("id"));
				group.setName(rs.getString("name"));
				group.setRemark(rs.getString("remark"));
				group.setCreateTime(rs.getString("create_time"));
				group.setState(rs.getInt("state"));
				group.setWithdrawTime(rs.getString("withdraw_time"));
				return group;
			}
		};
		return jdbcTemplate.queryForObject(sql, mapper, new Object[] { id });
	}

	public List<String> getMenuByGroup(String groupId) {
		String sql = "SELECT menu_id FROM  group_menu WHERE  group_id = ?";
		RowMapper<String> mapper = new RowMapper<String>() {

			public String mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getString("menu_id");
			}
		};
		return jdbcTemplate.query(sql, mapper, new Object[] { groupId });
	}

	public int updateGroup(Group group) {
		String sql = "UPDATE  group_info SET name = :name,remark = :remark,state = :state ,withdraw_time = :withdraw_time WHERE id = :id";
		Map<String, Object> parameters = new HashMap<String, Object>();

		parameters.put("name", group.getName());
		parameters.put("remark", group.getRemark());
		parameters.put("state", group.getState());
		parameters.put("withdraw_time", group.getWithdrawTime());
		parameters.put("id", group.getId());
		return jdbcTemplate.update(sql, parameters);
	}

	public int addGroup(Group group) {
		logger.info("***:" + group.getId());
		String sql = "INSERT INTO group_info(id,name,remark,create_time,state,withdraw_time) VALUES"
				+ "(:id,:name,:remark,:create_time,:state,:withdraw_time)";
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("id", group.getId());
		parameters.put("name", group.getName());
		parameters.put("remark", group.getRemark());
		parameters.put("create_time", group.getCreateTime());
		parameters.put("state", group.getState());
		parameters.put("withdraw_time", group.getWithdrawTime());
		return jdbcTemplate.update(sql, parameters);
	}

	public List<Map<String, Object>> getGroupIdByGroupName(String name) {
		String sql = "SELECT id FROM  group_info WHERE NAME = ?";
		return jdbcTemplate.queryForList(sql, new Object[] { name });
	}

	public int[] addGroupMenu(final String id, String[] menus) {

		String sql = "INSERT INTO group_menu (group_id,menu_id)VALUES"
				+ "(?,?)";

		List<Object[]> batch = new ArrayList<Object[]>();
		for (String menu : menus) {
			Object[] values = new Object[] { id, menu };
			batch.add(values);
		}

		return jdbcTemplate.batchUpdate(sql, batch);
	}

	public int[] deleteGroup(final List<Group> groups) {
		String sql = "UPDATE group_info SET state = :state , withdraw_time = :withdraw_time WHERE id = :id";
		SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(groups
				.toArray());
		return jdbcTemplate.batchUpdate(sql, batch);
	}

	public List<User> getAllUser(JqGridParameter jqGridParameter) {
		String sql, sql1, sql2;
		sql1 = "SELECT user.id,user.loginName,user.password,user.name,user.email,"
				+ "user.tel,group_info.name,user.group_id,user.create_time,user.state,user.withdraw_time "
				+ "FROM USER LEFT JOIN group_info  ON user.group_id=group_info.id	 ";

		sql2 = " order by user." + jqGridParameter.getSidx() + " "
				+ jqGridParameter.getSord() + " ";

		sql = sql1 + sql2;

		// 分页处理
		sql = PaginationUtils.paginationSQLForMySQL(sql,
				jqGridParameter.getPage(), jqGridParameter.getRows());

		RowMapper<User> mapper = new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getString("user.id"));
				user.setLoginName(rs.getString("user.loginName"));
				user.setPassword(rs.getString("user.password"));
				user.setName(rs.getString("user.name"));
				user.setEmail(rs.getString("user.email"));
				user.setTel(rs.getString("user.tel"));
				user.setGroupName(rs.getString("group_info.name"));
				user.setGroupId(rs.getString("user.group_id"));
				user.setCreateTime(rs.getString("user.create_time"));
				user.setState(rs.getInt("user.state"));
				user.setWithdrawTime(rs.getString("user.withdraw_time"));
				return user;
			}
		};
		return jdbcTemplate.query(sql, mapper);
	}

	public long getAllUserCount(JqGridParameter jqGridParameter) {
		String sql = "SELECT COUNT(id) FROM USER ";
		return jdbcTemplate.queryForLong(sql);
	}

	public List<User> getAllUserByParameter(JqGridParameter jqGridParameter) {

		String sql, sql1, sql2, sql3;

		List<Criterion> criteria = new ArrayList<Criterion>();

		criteria.addAll(JqGridUtils
				.generateSearchCriteriaFromFilters(jqGridParameter.getFilters()));

		sql1 = "SELECT id,loginName,password,name,email,tel,group_id,create_time,state,withdraw_time FROM user ";

		Object[] parameters = new Object[criteria.size()];

		int index = 0;

		sql2 = JqGridUtils.converToSql(criteria, "user");

		for (int i = 0; i < criteria.size(); i++) {
			parameters[index] = criteria.get(i).getValue();
			index++;
		}
		Object[] paramEnd = new Object[index];

		sql3 = " order by " + jqGridParameter.getSidx() + " "
				+ jqGridParameter.getSord() + "";

		System.arraycopy(parameters, 0, paramEnd, 0, index);

		sql = sql1 + sql2 + sql3;

		// 分页
		sql = PaginationUtils.paginationSQLForMySQL(sql,
				jqGridParameter.getPage(), jqGridParameter.getRows());

		RowMapper<User> mapper = new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getString("id"));
				user.setLoginName(rs.getString("loginName"));
				user.setPassword(rs.getString("password"));
				user.setName(rs.getString("name"));
				user.setEmail(rs.getString("email"));
				user.setTel(rs.getString("tel"));
				user.setGroupId(rs.getString("group_id"));
				user.setCreateTime(rs.getString("create_time"));
				user.setState(rs.getInt("state"));
				user.setWithdrawTime(rs.getString("withdraw_time"));
				return user;
			}
		};

		return jdbcTemplate.query(sql, mapper, paramEnd);
	}

	public long getAllUserByParameterCount(JqGridParameter jqGridParameter) {
		String sql, sql1, sql2;
		sql1 = "SELECT COUNT(id) FROM USER ";
		List<Criterion> criteria = new ArrayList<Criterion>();

		criteria.addAll(JqGridUtils
				.generateSearchCriteriaFromFilters(jqGridParameter.getFilters()));

		sql2 = JqGridUtils.converToSql(criteria, "");

		sql = sql1 + sql2;
		Object[] parameters = new Object[criteria.size()];
		int index = 0;
		for (int i = 0; i < criteria.size(); i++) {
			parameters[index] = criteria.get(i).getValue();
			index++;
		}
		Object[] paramsEnd = new Object[index];
		System.arraycopy(parameters, 0, paramsEnd, 0, index);
		return jdbcTemplate.queryForLong(sql, paramsEnd);
	}

	public int updateUser(User user) {
		String sql = "update user set loginName = :loginName , password = :password, name = :name,"
				+ "email = :email,tel = :tel,group_id = :group_id,state = :state,withdraw_time = :withdraw_time "
				+ "where id = :id";
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("id", user.getId());
		parameters.put("loginName", user.getLoginName());
		parameters.put("password", user.getPassword());
		parameters.put("name", user.getName());
		parameters.put("email", user.getEmail());
		parameters.put("tel", user.getTel());
		parameters.put("group_id", user.getGroupId());
		parameters.put("state", user.getState());
		parameters.put("withdraw_time", user.getWithdrawTime());
		return jdbcTemplate.update(sql, parameters);
	}

	public int addUser(User user) {
		String sql = "INSERT INTO user(id,loginName,password,name,email,tel,group_id,create_time,state,withdraw_time) "
				+ "VALUES(:id,:loginName,:password,:name,:email,:tel,:group_id,:create_time,:state,:withdraw_time)";
		String uuid = UUID.randomUUID().toString();

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("id", uuid);
		parameters.put("loginName", user.getLoginName());
		parameters.put("password", user.getPassword());
		parameters.put("name", user.getName());
		parameters.put("email", user.getEmail());
		parameters.put("tel", user.getTel());
		parameters.put("group_id", user.getGroupId());
		parameters.put("create_time", user.getCreateTime());
		parameters.put("state", user.getState());
		parameters.put("withdraw_time", user.getWithdrawTime());
		return jdbcTemplate.update(sql, parameters);
	}

	public User getUserByLoginName(String loginName) {
		
		logger.info("dao");
		
		String sql = "SELECT id,loginName,password,name,email,tel,group_id,create_time,state,withdraw_time "
				+ "FROM USER WHERE loginName = ?";

		RowMapper<User> mapper = new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getString("id"));
				user.setLoginName(rs.getString("loginName"));
				user.setPassword(rs.getString("password"));
				user.setName(rs.getString("name"));
				user.setEmail(rs.getString("email"));
				user.setTel(rs.getString("tel"));
				user.setGroupId(rs.getString("group_id"));
				user.setCreateTime(rs.getString("create_time"));
				user.setState(rs.getInt("state"));
				user.setWithdrawTime(rs.getString("withdraw_time"));
				return user;
			}
		};
		List<User> list = jdbcTemplate.query(sql, mapper, new Object[]{loginName});
		if(list.size() != 0){
			return list.get(0);
		} 
		return  new User();
	}

	public User getUserById(String id) {
		String sql = "SELECT id,loginName,password,name,email,tel,group_id,create_time,state,withdraw_time FROM USER WHERE id = ? ";
		RowMapper<User> mapper = new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getString("id"));
				user.setLoginName(rs.getString("loginName"));
				user.setPassword(rs.getString("password"));
				user.setName(rs.getString("name"));
				user.setEmail(rs.getString("email"));
				user.setTel(rs.getString("tel"));
				user.setGroupId(rs.getString("group_id"));
				user.setCreateTime(rs.getString("create_time"));
				user.setState(rs.getInt("state"));
				user.setWithdrawTime(rs.getString("withdraw_time"));
				return user;
			}
		};
		return jdbcTemplate.queryForObject(sql, mapper, new Object[] { id });

	}

	public int[] deleteUser(final List<User> userList) {

		for (int i = 0; i < userList.size(); i++) {
			logger.info("id:" + userList.get(i).getId());
		}

		String sql = "UPDATE user SET state = ? , withdraw_time = ? WHERE id = ?";

		List<Object[]> batch = new ArrayList<Object[]>();

		for (User user : userList) {
			Object[] values = new Object[] { user.getState(),
					user.getWithdrawTime(), user.getId() };
			batch.add(values);

		}
		return jdbcTemplate.batchUpdate(sql, batch);
	}

	public int deleteGroupMenu(String groupId) {
		String sql = "DELETE FROM  group_menu  WHERE  group_id = ? ";
		return jdbcTemplate.update(sql, new Object[] { groupId });
	}

	public List<Map<String, Object>> getAllGroupToOptions() {
		String sql = "SELECT id,NAME FROM  group_info";
		return jdbcTemplate.queryForList(sql);
	}

	public int updatePassword(User user) {
		String sql = "UPDATE user SET  password  = :password WHERE id = :id";
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("id", user.getId());
		parameters.put("password", user.getPassword());
		return jdbcTemplate.update(sql, parameters);
	}

	@Override
	public List<Menu> getMenuByGroupId(String groupId) {
		String sql = "SELECT  m.id,m.name,m.url,m.pid FROM group_menu AS gm "
				+ "LEFT JOIN menu AS m ON m.id = gm.menu_id WHERE gm.group_id = ? ORDER BY m.id   ";
		RowMapper<Menu> mapper = new RowMapper<Menu>() {

			@Override
			public Menu mapRow(ResultSet rs, int rowNum) throws SQLException {
				Menu menu = new Menu();
				menu.setId(rs.getInt("m.id"));
				menu.setName(rs.getString("m.name"));
				menu.setUrl(rs.getString("m.url"));
				menu.setPid(rs.getString("m.pid"));
				return menu;
			}
		};
		return jdbcTemplate.query(sql, mapper, new Object[] { groupId });
	}

}
